<?php
$server = "172.21.147.31";
$connectionInfo = array( "Database"=>"ss2g6", "UID"=>"ss2g6", "PWD"=>"group6", 'ReturnDatesAsStrings'=> true   );
$conn = sqlsrv_connect( $server, $connectionInfo );

if ($conn == false){
	 die( print_r( sqlsrv_errors(), true));
}

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

if(isset($_GET['update'])){
    $updateCompanyName = $_GET['companyName'];
    $updateTradeName = $_GET['tradeName'];
    $updatePatientId = $_GET['patientId'];
    $updateDoctorId = $_GET['doctorId'];
    $updateDate = $_GET['date'];

    $sql = "SELECT * FROM Prescribes WHERE companyName = '$updateCompanyName' AND tradeName = '$updateTradeName' AND patientId = $updatePatientId AND doctorId = $updateDoctorId AND date = '$updateDate'";
	$result = sqlsrv_query($conn, $sql, $params, $options);
    $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

    $updateCompanyName = $row['companyName'];
    $updateTradeName = $row['tradeName'];
    $updatePatientId = $row['patientId'];
    $updateDoctorId = $row['doctorId'];
    $updateDate = $row['date'];
    $updateData = $row['data'];
    $updateQuantity = $row['quantity'];
}

if(isset($_POST['Update'])){
    $updateCompanyNameOld = $_POST['companyNameOld'];
    $updateTradeNameOld = $_POST['tradeNameOld'];
    $updatePatientIdOld = $_POST['patientIdOld'];
    $updateDoctorIdOld = $_POST['doctorIdOld'];
    $updateDateOld = $_POST['dateOld'];

    $updateCompanyName = $_POST['companyName'];
    $updateTradeName = $_POST['tradeName'];
    $updatePatientId = $_POST['patientId'];
    $updateDoctorId = $_POST['doctorId'];
    $updateDate = $_POST['date'];
    $updateData = $_POST['data'];
    $updateQuantity = $_POST['quantity'];

    /* check empty fields */
	if($updateQuantity == "" || $updateDate == ""){
		$validation_error_1 = true;
	}
	else {
		$validation_error_1 = false;
	}
	
	/*check whether phoneNum is a numeric number */
	if(is_numeric($updateQuantity)){
		$numeric_1 = true;
	}
	else {
		$numeric_1 = false;
	} 
	
    $sql = "UPDATE Prescribes SET companyName = '$updateCompanyName', tradeName = '$updateTradeName', patientId = $updatePatientId, doctorId = $updateDoctorId, date = '$updateDate', data='$updateData', quantity='$updateQuantity '";
    $sql = $sql . "WHERE companyName = '$updateCompanyNameOld' AND tradeName = '$updateTradeNameOld' AND patientId = $updatePatientIdOld AND doctorId = $updateDoctorIdOld ANd date = '$updateDateOld'";
		
	if($validation_error_1 == false && $numeric_1 == true){
		$result = sqlsrv_query( $conn, $sql, $params, $options );
		if ($result == false){
			$error_update = true;
		}
		else{
			$error_update = false;
		}
		$confirm = "The prescription is updated.";
	}
	else {
		$confirm = "";
	}
}

if(isset($_POST['Insert'])) {
    $companyName = $_POST['companyName'];
    $tradeName = $_POST['tradeName'];
    $patientId = $_POST['patientId'];
    $doctorId = $_POST['doctorId'];
    $data = $_POST['data'];
    $date = $_POST['date'];
    $quantity = $_POST['quantity'];
	
	/* check empty fields */
	if($quantity == "" || $date == ""){
		$validation_error = true;
	}
	else {
		$validation_error = false;
	}
	
	/*check whether phoneNum is a numeric number */
	if(is_numeric($quantity)){
		$numeric = true;
	}
	else {
		$numeric = false;
	} 
	
    if($data == ""){
          $sql = "INSERT INTO Prescribes VALUES ('$companyName', '$tradeName', '$patientId', '$doctorId', NULL, '$date', '$quantity')";
    }
    else{
        $sql = "INSERT INTO Prescribes VALUES ('$companyName', '$tradeName', '$patientId', '$doctorId', '$data', '$date', '$quantity')";
    }
	
	if($validation_error == false && $numeric == true){
		$result = sqlsrv_query( $conn, $sql, $params, $options );
		if ($result == false){
			$error_insert = true;
		}
		else{
			$error_insert = false;
		}
		$confirm = "The prescription is inserted.";
	}
	else {
		$confirm = "";
	}
    
}

if(isset($_GET['delete'])){
	$companyName = $_GET['companyName'];
	$tradeName = $_GET['tradeName'];
	$patientId = $_GET['patientId'];
	$doctorId = $_GET['doctorId'];
    $date = $_GET['date'];
	$sql = "DELETE FROM Prescribes WHERE companyName = '". $companyName . "' AND tradeName='".$tradeName."' AND patientId='".$patientId."' AND doctorId='".$doctorId."' AND date='".$date."'";
	$params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query( $conn, $sql, $params, $options );
		if ($result == false){
		$error = true;
	}
	else {
		$error = false;
	}
}
if (isset($_POST['submit'])){
    $sql1 = $_POST['query'];
}
else
{
    $sql = "SELECT * FROM Prescribes";
}
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );
?>

<? include 'html_head.php' ?>
    <? include 'header.php' ?>

    <div class="container-fluid">
      <div class="row-fluid">
        <div class="span2">
                <? include 'sidebar.php' ?>
        </div><!--/span-->
        <div class="span9">
        <h2>Prescribes</h2>
        <hr>
		<?
		if(isset($_GET['delete'])){
			if ($error){ ?>
				<div class="alert alert-error">
					<? echo "Error in deleting. Foreign Key constraint violated.<br />";?>
				</div>
				
			<? 
			} 
			else { ?>
				<div class="alert alert-success">
				<? echo "Data deleted successfully."?>
				</div>
				
			<?
			} 
		}?>

        <?
			if(isset($_POST['Update'])){
				$msg = "";
				if($numeric_1 == false){ 
					$msg = "Quantity's text field must be an integer value <br />";
				}
				if($validation_error_1){
					$msg = $msg . "No text fields can be empty except 'Data' ";
				}
				if(isset($error_update) && $error_update){
					$msg = "Data cannot be updated, foreign key constraints or triggers violated <br />";
				}
				
				if ($msg <> ""){ ?>
					<div class="alert alert-error">
						<? echo $msg; ?>
					</div>
				<?
				}else { ?>
					<div class="alert alert-success">
					<? echo $confirm;?>
					</div>
					
				<?
				} 
			}?>
		<?
			if(isset($_POST['Insert'])){
				$msg = "";
				if($numeric == false){ 
					$msg = "Quantity's text field must be an integer value <br />";
				}
				if($validation_error){
					$msg = $msg . "No text fields can be empty except 'Data' ";
				}
				if(isset($error_insert) && $error_insert){
					$msg = "Data cannot be inserted, foreign key constraints or triggers violated <br />";
				}
				
				if ($msg <> ""){ ?>
					<div class="alert alert-error">
						<? echo $msg; ?>
					</div>
				<?
				}else { ?>
					<div class="alert alert-success">
					<? echo $confirm;?>
					</div>
					
				<?
				} 
			}?>
        <?
            
		if(sqlsrv_has_rows($stmt))
		{		
			$row_count = sqlsrv_num_rows($stmt);
            $colnames = sqlsrv_field_metadata($stmt)
		?>
		
		<table class="table table-striped table table-condensed">
		
		<thead>
			<tr>
			  <?
                    echo "<th>Company Name</th>";
					echo "<th>Trade Name</th>";
					echo "<th>Patient ID</th>";
                    echo "<th>Doctor ID</th>";
					echo "<th>Data</th>";
					echo "<th>Date</th>";
					echo "<th>Quantity</th>";
                ?>
			</tr>	
			</thead>
		<tbody>
		<?
			while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
        ?>
			<tr>
				<?
					foreach ($colnames as &$colname){
					$cellname = $colname['Name'];
					echo "<td>";
					if ($colname['Type'] == 93){
						$temp_date = explode(" ", $row[$cellname]);
						echo $temp_date[0];
					}
					else{
						echo $row[$cellname];
					}
					echo "</td>";
					}
					$tradeName = urlencode($row['tradeName']);
					$companyName = urlencode($row['companyName']);
					$patientId = urlencode($row['patientId']);
					$doctorId = urlencode($row['doctorId']);
                    $date = urlencode($row['date']);
					echo "<td><a href='prescribes.php?delete=true&tradeName=" . $tradeName . "&date=" .$date. "&companyName=" . $companyName .  "&patientId=" . $patientId .  "&doctorId=" . $doctorId . "'>Delete</a></td>";
					echo "<td><a href='prescribes.php?update=true&tradeName=" . $tradeName . "&date=" .$date. "&companyName=" . $companyName .  "&patientId=" . $patientId .  "&doctorId=" . $doctorId . "#updates'>Update</a></td>";
				?>
			</tr>
		  
		<?
			}
		?>
		</tbody>
		</table>
		<?
		}
		else{
			echo "<h2>No records.</h2>";
		}
		?>
          <div class="row-fluid">
            <div class="span12">


            <? if (isset($_GET['update'])){ ?>
            <div>
                	<a name="updates"></a><h3>Update Prescribes information:</h3>
                    <form method="post" action="prescribes.php" class="well form-horizontal">

                    <input type="hidden" name="companyNameOld" value="<? echo $updateCompanyName; ?>" />
                    <input type="hidden" name="tradeNameOld" value="<? echo $updateTradeName; ?>" />
                    <input type="hidden" name="patientIdOld" value="<? echo $updatePatientId; ?>" />
                    <input type="hidden" name="doctorIdOld" value="<? echo $updateDoctorId; ?>" />
                    <input type="hidden" name="dateOld" value="<? echo $updateDate ?>" />

                    <div class="control-group">
                        <label class="control-label">Company Name</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT companyName FROM PharmaceuticalCompany order by companyName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'companyName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
                                    echo "<option ";
                                    if ($data['companyName'] == $updateCompanyName){
                                        echo "selected=selected";
                                    }
                                    echo ">";
									echo $data['companyName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="companyName" id="companyName" placeholder="eg. Alibaba Medical Centre" />-->
                            <span class="help-inline"><a href="pharmaceutical.php" target="_blank">Create new PharmaceuticalCompany</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Trade Name</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT tradeName FROM Drug order by tradeName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'tradeName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
                                    echo "<option ";
                                    if ($data['tradeName'] == $updateTradeName){
                                        echo "selected=selected";
                                    }
                                    echo ">";
									echo $data['tradeName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="tradeName" id="tradeName" placeholder="eg. Panadol" />-->
                            <span class="help-inline"><a href="drugs.php" target="_blank">Create new Drug</a></span>
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Patient ID</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT patientId FROM Patient order by patientId ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'patientId'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
                                    echo "<option ";
                                    if ($data['patientId'] == $updatePatientId){
                                        echo "selected=selected";
                                    }
                                    echo ">";
									echo $data['patientId']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!-- <input type="text" name="patientId" id="patientId" placeholder="ID of the patient" />-->
                            <span class="help-inline"><a href="patients.php" target="_blank">Create new Patient</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Doctor ID</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT doctorId FROM Doctor order by doctorId ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'doctorId'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
                                    echo "<option ";
                                    if ($data['doctorId'] == $updateDoctorId){
                                        echo "selected=selected";
                                    }
                                    echo ">";
									echo $data['doctorId']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="doctorId" id="doctorId" placeholder="Id of doctor" />-->
                            <span class="help-inline"><a href="doctors.php" target="_blank">Create new Doctor</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Data</label>
                        <div class="controls">
                        <textarea class="span5" name="data" id="data"><? echo $updateData; ?></textarea> </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Date</label>
                        <div class="controls"> 
                        <input type="text" name="date" id="date" value="<? echo $updateDate; ?>"/>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Quantity</label>
                        <div class="controls"> 
                        <input type="text" name="quantity" id="quantity" value="<? echo $updateQuantity; ?>"/>
                        </div>
                    </div>
                     <div class="form-actions">
                        <input type="submit" name="Update" value="Update"  class="btn btn-primary"/>
                    </div> 
                    </form>   
            </div>
            <? } ?>

            <h3>Create a new Prescribes information:</h3> 
                    <form method="post" action="prescribes.php" class="well form-horizontal">
                    <div class="control-group">
                        <label class="control-label">Company Name</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT companyName FROM PharmaceuticalCompany order by companyName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'companyName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['companyName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="companyName" id="companyName" placeholder="eg. Alibaba Medical Centre" />-->
                            <span class="help-inline"><a href="pharmaceutical.php" target="_blank">Create new PharmaceuticalCompany</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Trade Name</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT tradeName FROM Drug order by tradeName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'tradeName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['tradeName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="tradeName" id="tradeName" placeholder="eg. Panadol" />-->
                            <span class="help-inline"><a href="drugs.php" target="_blank">Create new Drug</a></span>
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Patient ID</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT patientId FROM Patient order by patientId ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'patientId'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['patientId']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!-- <input type="text" name="patientId" id="patientId" placeholder="ID of the patient" />-->
                            <span class="help-inline"><a href="patients.php" target="_blank">Create new Patient</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Doctor ID</label>
                        <div class="controls">
                            <? 
								$sql2 = "SELECT doctorId FROM Doctor order by doctorId ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'doctorId'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['doctorId']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="doctorId" id="doctorId" placeholder="Id of doctor" />-->
                            <span class="help-inline"><a href="doctors.php" target="_blank">Create new Doctor</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Data</label>
                        <div class="controls">
                            <textarea class="span5" name="data" id="data" placeholder="eg. Once every four hours."></textarea> </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Date</label>
                        <div class="controls"> 
                            <input type="text" name="date" id="date" placeholder="eg. YYYY-MM-DD"/>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Quantity</label>
                        <div class="controls"> 
                            <input type="text" name="quantity" id="quantity" placeholder="eg. 20"/>
                        </div>
                    </div>
                     <div class="form-actions">
                        <input type="submit" name="Insert" value="Insert"  class="btn btn-primary"/>
                    </div> 
                    </form>
              <h3>Enter your query:</h3>
                    <form method="post" action="custom.php" class="well form-inline">
                    <div class="control-group">
                        <div class="controls">
                            <textarea name="query" class="span10" id="textarea" rows="5" placeholder = 'SQL query'></textarea>
                        </div>
                    </div>
                    <p>
                    <p><input type="submit" name="submit" class="btn btn-primary" href="#" /></p>
                    </form>
                    
            </div><!--/span-->
          </div><!--/row-->
        </div><!--/span-->
      </div><!--/row-->

      <hr>

      <footer>
        <p>&copy; SS2 GROUP 6 </p>
      </footer>

    </div><!--/.fluid-container-->
  </body>
</html>
